How to create a chart in Excel 2007

The following code creates a chart in Excel 2003 without any errors. However, when I run the code with Excel 2007, I get the error "null OleAutoObj parameter was passed into argument position 1" at line 21. I've verified that the line oleGet(objSheet, "ChartObjects", objCharts) at line 15 returns a null value for objCharts.

Does anyone know how to create a chart in Excel 2007?
 

OleAutoObj objExcel = oleCreateAutoObject "Excel.Application"
olePut(objExcel, "Visible", true) 
OleAutoObj objWrkBks = null
oleGet(objExcel, "Workbooks", objWrkBks)
 
OleAutoArgs args = create()
OleAutoObj objWrkBk = null
put(args, 1)
oleMethod(objWrkBks, "Add", args, objWrkBk)
OleAutoObj objSheet
oleGet(objExcel, "ActiveSheet", objSheet)
 
OleAutoObj objCharts = null
OleAutoObj objChart = null
oleGet(objSheet, "ChartObjects", objCharts) // LINE 15
clear args
put(args, 100)
put(args, 100)
put(args, 100)
put(args, 100)
oleMethod(objCharts, "Add", args, objChart) // LINE 21

Re: How to create a chart in Excel 2007
Mathias Mamsch - Sun Mar 20 19:41:09 EDT 2011

ChartObjects is not longer a property but a method of the worksheet. See here. Therefore you need to use oleMethod to aquire a handle to it.
 

OleAutoArgs args = create()
OleAutoObj objWrkBks = null, objWrkBk = null, objSheet = null, objCharts = null, objChart = null
OleAutoObj objExcel = oleCreateAutoObject "Excel.Application"
olePut(objExcel, "Visible", true) 
oleGet(objExcel, "Workbooks", objWrkBks)
 
put(args, 1); oleMethod(objWrkBks, "Add", args, objWrkBk)
oleGet(objExcel, "ActiveSheet", objSheet)
 
clear args; oleMethod(objSheet, "ChartObjects", args, objCharts) // use oleMethod here
clear args; put(args, 100); put(args, 100); put(args, 100); put(args, 100)
oleMethod(objCharts, "Add", args, objChart)

Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

Re: How to create a chart in Excel 2007
mgov - Mon Mar 21 10:21:56 EDT 2011